var SPREADSHEET_ID = '1uEm-9g1MXSzSx8LpZlnUwfdmHl2qV7v-rCEFAwg-nVQ';

// ===== FUNGSI doGet (WAJIB untuk Web App) =====
function doGet(e) {
  try {
    const mode = e.parameter.mode;
    
    if (mode === 'test') {
      return ContentService
        .createTextOutput(JSON.stringify({ 
          status: 'ok', 
          message: 'API is working!', 
          time: new Date().toLocaleString('id-ID', { timeZone: 'Asia/Jakarta' })
        }))
        .setMimeType(ContentService.MimeType.JSON);
    }
    
    return ContentService
      .createTextOutput(JSON.stringify({ status: 'error', message: 'Use mode=test for GET request' }))
      .setMimeType(ContentService.MimeType.JSON);
      
  } catch (error) {
    return ContentService
      .createTextOutput(JSON.stringify({ status: 'error', message: error.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

// ===== FUNGSI doPost =====
function doPost(e) {
  try {
    const mode = e.parameter.mode;
    const sheetName = e.parameter.sheet;
    const data = JSON.parse(e.parameter.data);
    
    Logger.log('Mode: ' + mode);
    Logger.log('Sheet: ' + sheetName);
    Logger.log('Data mode: ' + (data.mode || 'full'));
    Logger.log('Siswa count: ' + (data.siswa ? data.siswa.length : 0));
    Logger.log('Mapel count: ' + (data.mapel ? data.mapel.length : 0));
    
    if (mode === 'kirim') {
      let result;
      
      // Mode Reset
      if (data.mode === 'reset') {
        result = resetSheet(sheetName);
      } else if (data.mode === 'single') {
        result = updateSingleMapelV2(sheetName, data);
      } else {
        result = updateFullSheet(sheetName, data);
      }
      
      return ContentService
        .createTextOutput(JSON.stringify({ status: 'success', message: result }))
        .setMimeType(ContentService.MimeType.JSON);
    }
    
    return ContentService
      .createTextOutput(JSON.stringify({ status: 'error', message: 'Invalid mode' }))
      .setMimeType(ContentService.MimeType.JSON);
      
  } catch (error) {
    Logger.log('Error: ' + error.toString());
    return ContentService
      .createTextOutput(JSON.stringify({ status: 'error', message: error.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

// ===== FUNGSI: Reset Sheet (Mengosongkan) =====
function resetSheet(sheetName) {
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  let sheet = spreadsheet.getSheetByName(sheetName);
  
  Logger.log('resetSheet - Sheet: ' + sheetName);
  
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
    Logger.log('Sheet created: ' + sheetName);
  } else {
    sheet.clear();
    Logger.log('Sheet cleared: ' + sheetName);
  }
  
  const headers = ['No', 'NIS/NISN', 'Nama Lengkap'];
  if (headers.length > 0) {
    const headerRange = sheet.getRange(1, 1, 1, headers.length);
    headerRange.setValues([headers]);
    headerRange.setFontWeight('bold');
    headerRange.setBackground('#2ecc71');
    headerRange.setFontColor('#ffffff');
  }
  
  sheet.setFrozenRows(1);
  sheet.autoResizeColumns(1, headers.length);
  
  Logger.log('Sheet reset completed: ' + sheetName);
  
  return 'Sheet "' + sheetName + '" telah direset (dikosongkan).';
}

// ===== FUNGSI BARU: Mencari kolom berdasarkan nama mapel =====
function findColumnByMapelName(sheet, mapelName) {
  const lastCol = sheet.getLastColumn();
  if (lastCol < 4) return -1;
  
  const headers = sheet.getRange(1, 4, 1, lastCol - 3).getValues()[0];
  
  for (let i = 0; i < headers.length; i++) {
    const headerText = headers[i].toString();
    // Cek apakah header mengandung nama mapel
    if (headerText.includes(mapelName)) {
      return i + 4; // +4 karena kolom 1-3 adalah No, NIS, Nama
    }
  }
  return -1; // Tidak ditemukan
}

// ===== FUNGSI BARU: Sinkronisasi header spreadsheet dengan database =====
function syncHeaders(sheet, allMapel, allKkm) {
  const currentHeaders = [];
  const lastCol = sheet.getLastColumn();
  
  // Ambil header yang ada (kolom 4 ke atas)
  if (lastCol >= 4) {
    const existingHeaders = sheet.getRange(1, 4, 1, lastCol - 3).getValues()[0];
    for (let i = 0; i < existingHeaders.length; i++) {
      currentHeaders.push(existingHeaders[i].toString());
    }
  }
  
  // Tambahkan mapel yang belum ada
  let nextCol = Math.max(4, lastCol + 1);
  const mapelIndexMap = {};
  
  allMapel.forEach((mapel) => {
    const mapelName = mapel.nama;
    const kkm = allKkm[mapelName] || 70;
    const expectedHeader = '(' + kkm + ') ' + mapelName;
    
    // Cari apakah mapel sudah ada di header
    let foundIndex = -1;
    for (let i = 0; i < currentHeaders.length; i++) {
      if (currentHeaders[i].includes(mapelName)) {
        foundIndex = i;
        // Update header jika KKM berubah
        if (currentHeaders[i] !== expectedHeader) {
          sheet.getRange(1, i + 4).setValue(expectedHeader);
          currentHeaders[i] = expectedHeader;
        }
        break;
      }
    }
    
    if (foundIndex >= 0) {
      mapelIndexMap[mapelName] = foundIndex;
    } else {
      // Mapel belum ada, tambahkan di kolom baru
      mapelIndexMap[mapelName] = currentHeaders.length;
      currentHeaders.push(expectedHeader);
      sheet.getRange(1, nextCol).setValue(expectedHeader);
      sheet.getRange(1, nextCol).setFontWeight('bold');
      sheet.getRange(1, nextCol).setBackground('#2ecc71');
      sheet.getRange(1, nextCol).setFontColor('#ffffff');
      
      // Tambahkan tanggal di baris 2
      sheet.getRange(2, nextCol).setValue('Update: -');
      sheet.getRange(2, nextCol).setFontStyle('italic').setFontColor('#666666');
      
      nextCol++;
    }
  });
  
  return mapelIndexMap;
}

// ===== FUNGSI BARU: updateSingleMapelV2 (dengan sinkronisasi header) =====
function updateSingleMapelV2(sheetName, data) {
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  let sheet = spreadsheet.getSheetByName(sheetName);
  
  const mapelName = data.mapel_name;
  const nilaiPerSiswa = data.nilai_per_siswa;
  const tanggal = data.tanggal;
  const allMapel = data.mapel || [];
  const allKkm = data.kkm || {};
  const siswaList = data.siswa || [];
  
  Logger.log('updateSingleMapelV2 - mapelName: ' + mapelName);
  Logger.log('updateSingleMapelV2 - tanggal: ' + tanggal);
  
  // Jika sheet belum ada, buat full sheet
  if (!sheet) {
    Logger.log('Sheet not found, creating full sheet');
    return updateFullSheet(sheetName, data);
  }
  
  // === Sinkronisasi header terlebih dahulu ===
  const mapelIndexMap = syncHeaders(sheet, allMapel, allKkm);
  const targetColumn = mapelIndexMap[mapelName] + 4; // +4 karena index 0 = kolom 4
  
  if (targetColumn === undefined) {
    Logger.log('ERROR: Mapel tidak ditemukan setelah sync');
    return updateFullSheet(sheetName, data);
  }
  
  Logger.log('Target column for ' + mapelName + ': ' + targetColumn);
  
  // Update header (pastikan KKM terbaru)
  const kkm = allKkm[mapelName] || 70;
  sheet.getRange(1, targetColumn).setValue('(' + kkm + ') ' + mapelName);
  sheet.getRange(1, targetColumn).setFontWeight('bold');
  sheet.getRange(1, targetColumn).setBackground('#2ecc71');
  sheet.getRange(1, targetColumn).setFontColor('#ffffff');
  
  // Update tanggal
  sheet.getRange(2, targetColumn).setValue('Update: ' + tanggal);
  sheet.getRange(2, targetColumn).setFontStyle('italic').setFontColor('#666666');
  
  // Pastikan jumlah baris cukup
  const requiredRows = siswaList.length + 2;
  const currentRows = sheet.getLastRow();
  if (currentRows < requiredRows) {
    sheet.insertRowsAfter(currentRows, requiredRows - currentRows);
  }
  
  // Update nilai - dengan mencocokkan No. Urut
  const existingNoColumn = sheet.getRange(3, 1, siswaList.length, 1).getValues();
  
  siswaList.forEach((siswa) => {
    // Cari baris berdasarkan No. Urut
    for (let rowIdx = 0; rowIdx < existingNoColumn.length; rowIdx++) {
      if (existingNoColumn[rowIdx][0] == siswa.no) {
        const rowNumber = rowIdx + 3;
        let nilai = '';
        
        if (nilaiPerSiswa && nilaiPerSiswa[siswa.no] !== undefined) {
          nilai = nilaiPerSiswa[siswa.no];
        } else if (siswa.nilai && siswa.nilai[mapelName] !== undefined) {
          nilai = siswa.nilai[mapelName];
        }
        
        if (nilai !== undefined && nilai !== '') {
          sheet.getRange(rowNumber, targetColumn).setValue(nilai);
        }
        break;
      }
    }
  });
  
  // Format kolom
  if (siswaList.length > 0) {
    sheet.getRange(3, targetColumn, siswaList.length, 1).setHorizontalAlignment('center');
  }
  
  Logger.log('Updated mapel: ' + mapelName + ' at column ' + targetColumn);
  
  return 'Updated mapel: ' + mapelName + ' (kolom ' + targetColumn + ', update: ' + tanggal + ')';
}

// ===== FUNGSI: updateSingleMapel (versi lama - fallback) =====
function updateSingleMapel(sheetName, data) {
  // Panggil versi baru
  return updateSingleMapelV2(sheetName, data);
}

// ===== FUNGSI: updateFullSheet =====
function updateFullSheet(sheetName, data) {
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  
  let sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  } else {
    sheet.clear();
  }
  
  const siswa = data.siswa || [];
  const mapelList = data.mapel || [];
  const kkmData = data.kkm || {};
  const waktuPerMapel = data.waktu_per_mapel || {};
  
  // Header
  const headers = ['No', 'NIS/NISN', 'Nama Lengkap'];
  mapelList.forEach(mapel => {
    const kkm = kkmData[mapel.nama] || 70;
    headers.push('(' + kkm + ') ' + mapel.nama);
  });
  
  // Tulis header
  if (headers.length > 0) {
    const headerRange = sheet.getRange(1, 1, 1, headers.length);
    headerRange.setValues([headers]);
    headerRange.setFontWeight('bold');
    headerRange.setBackground('#2ecc71');
    headerRange.setFontColor('#ffffff');
  }
  
  // Baris dengan waktu detail per mapel
  const tanggalRow = new Array(headers.length).fill('');
  tanggalRow[2] = 'Update:';
  mapelList.forEach((mapel, index) => {
    const colIndex = 4 + index;
    const waktu = waktuPerMapel[mapel.nama] || mapel.tanggal || data.tanggal || new Date().toLocaleString('id-ID', { timeZone: 'Asia/Jakarta' });
    tanggalRow[colIndex - 1] = waktu;
  });
  
  sheet.getRange(2, 1, 1, tanggalRow.length).setValues([tanggalRow]);
  sheet.getRange(2, 3, 1, headers.length - 2).setFontStyle('italic').setFontColor('#666666');
  
  // Data siswa
  const dataRows = [];
  siswa.forEach((s, index) => {
    const row = [];
    row.push(s.no || (index + 1));
    row.push(s.nis || '');
    row.push(s.nama || '');
    
    mapelList.forEach(mapel => {
      let nilai = '';
      if (s.nilai && s.nilai[mapel.nama] !== undefined) {
        nilai = s.nilai[mapel.nama];
      }
      row.push(nilai);
    });
    
    dataRows.push(row);
  });
  
  if (dataRows.length > 0) {
    sheet.getRange(3, 1, dataRows.length, headers.length).setValues(dataRows);
  }
  
  if (headers.length > 0) {
    sheet.autoResizeColumns(1, headers.length);
  }
  sheet.setFrozenRows(2);
  
  // Border
  const lastRow = dataRows.length + 2;
  if (lastRow >= 3 && headers.length > 0) {
    sheet.getRange(1, 1, lastRow, headers.length).setBorder(true, true, true, true, true, true);
  }
  
  if (headers.length > 3 && dataRows.length > 0) {
    sheet.getRange(3, 4, dataRows.length, headers.length - 3).setHorizontalAlignment('center');
  }
  
  return 'Full update: ' + dataRows.length + ' siswa, ' + mapelList.length + ' mapel';
}